﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION EXTERNALLOOKUP()
RETURNS 
@EXTLOOKUP TABLE 
(
	LOOKUPNAME nvarchar(100),
	ENTITYNAME nvarchar(100),
	ENTITYID nvarchar(100)
)
AS
BEGIN

	DECLARE @EXTVIEW nvarchar(max)
	DECLARE @COMBINEDVIEWS nvarchar(max)

	DECLARE ExternalViewsCursor cursor for
	SELECT sqlraw FROM
	  (
		SELECT
		SUBSTRING(RafikiNOSQL,CHARINDEX('{',RafikiNOSQL)+1,CHARINDEX('}',RafikiNOSQL)-CHARINDEX('{',RafikiNOSQL)-1)
		as extview
	 from DefinedEntityTypes) x
	JOIN DefinedExternalViews
	ON x.ExtView = DefinedExternalViews.ViewName
	SET @COMBINEDVIEWS = 'SELECT * FROM ('
	Open ExternalViewsCursor
	Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW
	WHILE @@FETCH_STATUS = 0 BEGIN
		IF (CHARINDEX('LOOKUPNAME',@EXTVIEW)>0)
			SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT LOOKUPNAME, ENTITYNAME, ENTITYID FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
		Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW
	END
	SET @COMBINEDVIEWS = SUBSTRING(@COMBINEDVIEWS,0,LEN(@COMBINEDVIEWS)-6) + ')'
	DECLARE @TSQL nvarchar(max)
	SET @TSQL='INSERT INTO @EXTLOOKUP(LOOKUPNAME, ENTITYNAME, ENTITYID) (SELECT LOOKUPNAME, ENTITYNAME, ENTITYID FROM OPENQUERY(PSPRODSERVER,'''+@COMBINEDVIEWS+'''))'
	EXECUTE @TSQL
	RETURN
END